﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace GongXianDCUI.CommonLib
{
    /// <summary>
    /// 数据库访问封装类
    /// </summary>
    public class SQLHepler
    {

        //公司台式机
        public static string connectionString = string.Empty;

        static SQLHepler()
        {
            connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        }

        /// <summary>
        /// 关闭对象，释放资源
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        protected static void CloseDispose(SqlDataReader dr, SqlCommand cmd, SqlConnection con)
        {
            if (dr != null)
            {
                dr.Close();
                dr.Dispose();
            }
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }

        #region 外部调用

        /// <summary>
        /// 分页查询,适用于SQLServer2008
        /// </summary>
        /// <param name="orderby">排序语句，比如id desc</param>
        /// <param name="tableName">表名称</param>
        /// <param name="sqlWhere">查询条件,从条件开始写，比如：“index=1 ”</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="total">总记录数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetPaging2008(string orderby, string tableName, string sqlWhere, int pageIndex, int pageSize, out decimal total)
        {           
            StringBuilder sqlText = new StringBuilder();
            if (pageSize == 0)
            {
                pageSize = 10;
            }
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            sqlText.Append(" select * from (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + ") as  RID,* from " + tableName);
            if (string.IsNullOrEmpty(sqlWhere) == false)
            {
                sqlText.Append(" where " + sqlWhere);//从条件开始写，比如：“ index=1 ”
            }
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            sqlText.Append(") as tt WHERE RID BETWEEN " + start + " AND " + end);
            //查询总条数
            string sql = "select count(1) from " + tableName;
            if (string.IsNullOrEmpty(sqlWhere) == false)
            {
                sql += " where " + sqlWhere;
            }
            SqlCommand comm = null;
            DataTable dt = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                comm = new SqlCommand(sqlText.ToString(), conn);
                SqlDataAdapter sda = new SqlDataAdapter(comm);
                dt = new DataTable();
                sda.Fill(dt);
                //查询总条数
                comm = new SqlCommand(sql, conn);
                object countObj = comm.ExecuteScalar();
                total = Convert.ToDecimal(countObj);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, comm, conn);
            }
            return dt;
        }

        /// <summary>
        /// 分页查询,仅限于SQLServer2012,低版本数据库无法使用
        /// </summary>
        /// <param name="orderby">排序语句</param>
        /// <param name="tableName">表名称</param>
        /// <param name="sqlWhere">查询条件,从条件开始写，比如：“index=1 ”</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="total">总记录数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetPaging2012(string orderby, string tableName, string sqlWhere, int pageIndex, int pageSize, out decimal total)
        {          
            StringBuilder sqlText = new StringBuilder();
            if (pageSize == 0)
            {
                pageSize = 10;
            }
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            sqlText.Append(" select * from " + tableName);
            if (string.IsNullOrEmpty(sqlWhere) == false)
            {
                sqlText.Append(" where " + sqlWhere);//从条件开始写，比如：“ index=1 ”
            }
            sqlText.Append(" order by " + orderby + " offset " + (pageIndex - 1) * pageSize + " rows fetch next " + pageSize + " rows only");
            //查询总条数
            string sql = "select count(1) from " + tableName;
            if (string.IsNullOrEmpty(sqlWhere) == false)
            {
                sql += " where " + sqlWhere;
            }
            SqlCommand comm = null;
            DataTable dt = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                comm = new SqlCommand(sqlText.ToString(), conn);
                SqlDataAdapter sda = new SqlDataAdapter(comm);
                dt = new DataTable();
                sda.Fill(dt);
                //查询总条数
                comm = new SqlCommand(sql, conn);
                object countObj = comm.ExecuteScalar();
                total = Convert.ToDecimal(countObj);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, comm, conn);
            }
            return dt;
        }

        /// <summary>
        /// 执行sql语句返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSql(string sql, params SqlParameter[] param)
        {
            SqlCommand cmd = null;
            SqlConnection conn = null;
            int count = 0;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(param);
                cmd.Prepare();
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, cmd, conn);
            }
            return count;
        }

        /// <summary>
        /// 执行sql语句，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] param)
        {
            SqlCommand cmd = null;
            DataTable dt = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = new SqlCommand(sql, conn);
                if (null != param)
                {
                    cmd.Parameters.AddRange(param);
                }
                cmd.Prepare();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                dt = new DataTable();
                sda.Fill(dt);
                sda.Dispose();
                cmd.Parameters.Clear();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, cmd, conn);
            }
            return dt;
        }

        /// <summary>
        /// 执行sql语句，返回DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>DataTable</returns>
        public static DataSet GetDataSet(string sql, params SqlParameter[] param)
        {
            SqlCommand cmd = null;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = new SqlCommand(sql, conn);
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                cmd.Prepare();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                sda.Fill(ds);
                sda.Dispose();
                cmd.Parameters.Clear();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, cmd, conn);
            }
            return ds;
        }

        /// <summary>
        /// 返回单值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数，单个或数组或为空</param>
        /// <returns></returns>
        public static object GetSingleValue(string sql, params SqlParameter[] param)
        {
            object obj = null;
            SqlCommand cmd = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = new SqlCommand(sql, conn);
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                cmd.Prepare();
                obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                CloseDispose(null, cmd, conn);
            }
            return obj;
        }

        /// <summary>
        /// 执行事务，返回受影响行数。参数列表，数组里面下标0为sql语句，1为SqlParameter[]参数
        /// </summary>
        /// <param name="cmdList">参数列表，数组里面下标0为sql语句，1为SqlParameter[]参数</param>
        /// <returns></returns>
        public static int ExecSqlTransacation(List<object[]> cmdList)
        {
            int count = 0;
            SqlCommand cmd = null;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection(connectionString);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                tran = conn.BeginTransaction();
                foreach (object[] arr in cmdList)
                {
                    string sql = arr[0] as string; //sql语句
                    SqlParameter[] parmes = arr[1] as SqlParameter[];//参数
                    cmd = new SqlCommand(sql, conn);
                    if (parmes != null)
                    {
                        cmd.Parameters.AddRange(parmes);
                    }
                    cmd.Prepare();
                    cmd.Transaction = tran;
                    int efCount = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    count += efCount;
                }
                tran.Commit();
            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                }
                CloseDispose(null, cmd, conn);
            }
            return count;
        }

        /// <summary>
        /// sqlserver语句过滤，返回过滤后的字符串
        /// </summary>
        /// <param name="str">传入字符串</param>
        /// <returns>返回过滤后的字符串</returns>
        public static string SqlFilter(string str)
        {
            if (!string.IsNullOrEmpty(str) && !string.IsNullOrWhiteSpace(str))
            {
                //关键字
                List<string> patternList = new List<string>() { "select", "insert", "delete", "from", "declare", "execute", "count\\(", "drop", "update", "truncate", "asc\\(", "mid\\(", "char\\(", "xp_cmdshell", "exec", "netlocalgroup administrators", "net user"};
                //特殊符号
                patternList.AddRange(new List<string> { "--", "'" });

                foreach (string item in patternList)
                {
                    str = Regex.Replace(str, item, "", RegexOptions.IgnoreCase);
                }
            }
            return str;
        }

        #endregion



    }
}
